package db;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Vector;

import config.SystemConfig;

public class FlightsManage {

	public static Vector<String[]> getFlightsByCities(String dest, String origin) {
		Vector<String[]> flightsArrayList = new Vector<String[]>(0);
		String sql = "SELECT f.*,c1.tax FROM flights f left join cities c on f.dest=c.id left join cities c1 on f.origin=c1.id"
				+ " where c.city='" + dest + "' and c1.city='" + origin + "'";
		System.out.println(sql);
		ResultSet rs;
		try {
			rs = DataBaseManager.getConnection().createStatement()
					.executeQuery(sql);

			while (rs.next()) {
				String[] tempStrings = new String[11];
				for (int i = 1; i <= 11; i++)
					tempStrings[i - 1] = rs.getString(i);
				flightsArrayList.add(tempStrings);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flightsArrayList;

	}

	public static boolean deleteFlight(int id) {
		String sql = "delete from flights where id=" + id;

		int r;
		try {
			r = DataBaseManager.getConnection().createStatement()
					.executeUpdate(sql);

			if (r == 1)
				return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	public static boolean insertAirline(String departure, String dest,
			double price, int seats, String time, String date, float discount,
			int tickets) {
		String sql = "insert into flights(start_date,start_time,seats,price,max_discount,min_buy,origin,dest)  values('"
				+ date

				+ "','"
				+ time
				+ "',"
				+ seats
				+ ","
				+ price
				+ ","
				+ discount
				+ ","
				+ tickets
				+ ","

				+ "(SELECT id FROM cities WHERE city='"
				+ departure
				+ "' LIMIT 0,1 ),"
				+ "(SELECT id FROM cities WHERE city='"
				+ dest + "' LIMIT 0,1 ))";
		System.out.println(sql);
		try {
			int r = DataBaseManager.getConnection().createStatement()
					.executeUpdate(sql);
			if (r == 1)
				return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	public static boolean updateAirline(int id, String departure, String dest,
			double price, int seats, String time, String date, float discount,
			int tickets) {

		String sql = "UPDATE  flights SET start_date='" + date

		+ "', start_time='" + time + "',seats=" + seats + ",price=" + price
				+ ",max_DISCOUNT=" + discount + ",MIN_buy=" + tickets + ","

				+ "" + "origin=(SELECT id FROM cities WHERE city='" + departure
				+ "' LIMIT 0,1 )," + "dest=(SELECT id FROM cities WHERE city='"
				+ dest + "' LIMIT 0,1 )" + "WHERE id=" + id;
		System.out.println(sql);
		try {
			int r = DataBaseManager.getConnection().createStatement()
					.executeUpdate(sql);
			if (r == 1)
				return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	public static Vector<Vector<String>> getAllFlights() {
		Vector<Vector<String>> flightsArrayList = new Vector<Vector<String>>(0);
		String sql = "SELECT f.id,f.start_date,f.start_time,c1.city from_ ,c.city to_,f.seats,f.price ,concat(f.max_discount,' % off (>=',f.min_buy,' tickets)')"
				+ "FROM flights f left join cities c on f.dest=c.id left join cities c1 on f.origin=c1.id";
		System.out.println(sql);
		ResultSet rs;
		try {
			rs = DataBaseManager.getConnection().createStatement()
					.executeQuery(sql);

			while (rs.next()) {
				Vector<String> tempStrings = new Vector<String>();
				for (int i = 1; i <= 8; i++)
					tempStrings.add(rs.getString(i));
				flightsArrayList.add(tempStrings);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flightsArrayList;

	}

	public static Vector<String> getFlights(int id) {
		Vector<String> info = new Vector<String>(0);
		String sql = "SELECT f.id ID0,f.start_date SD1,f.start_time ST2,c1.city DEPARTURE3 ,c.city DEST4,f.seats SEATS5,f.price PRICE6 ,f.max_discount DISCOUNT7,f.min_buy TICKETS8 "
				+ "FROM flights f left join cities c on f.dest=c.id left join cities c1 on f.origin=c1.id where f.id="
				+ id + " limit 0,1";
		System.out.println(sql);
		ResultSet rs;
		try {
			rs = DataBaseManager.getConnection().createStatement()
					.executeQuery(sql);

			if (rs.next()) {

				for (int i = 1; i <= 9; i++)
					info.add(rs.getString(i));

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return info;

	}

	public static Vector<String[]> getFlightsByDepartureDate(String date) {
		Vector<String[]> flightsArrayList = new Vector<String[]>(0);
		String sql = "select f.*,c.tax from flights f left join cities c on f.origin=c.id  where start_date='"
				+ date + "'";
		System.out.println(sql);
		ResultSet rs;
		try {
			rs = DataBaseManager.getConnection().createStatement()
					.executeQuery(sql);

			while (rs.next()) {
				String[] tempStrings = new String[11];
				for (int i = 1; i <= 11; i++)
					tempStrings[i - 1] = rs.getString(i);
				flightsArrayList.add(tempStrings);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flightsArrayList;

	}
	public static Vector<String[]> getFlightsForUser() {
		Vector<String[]> flightsArrayList = new Vector<String[]>(0);
		String sql = "select f.*,c.tax from flights f left join cities c on f.origin=c.id;";
		if(SystemConfig.offline)
		{
			for(int i=10;i<20;i++)
			{
				String[] temp=new String[11];
				temp[0]=i+"";
				temp[1]="2009-1-7";
				temp[2]=i+":20:00";
				temp[3]=150+"";
				temp[4]=(1500+i)+"";
				temp[5]=(i-9)+"";
				temp[6]=(i-8)+"";
				temp[7]="20";
				temp[8]="5";
				temp[9]=i+"";
				temp[10]="50";
				flightsArrayList.add(temp);
				
			}
			return flightsArrayList;
		}
		System.out.println(sql);
		ResultSet rs;
		try {
			rs = DataBaseManager.getConnection().createStatement()
					.executeQuery(sql);

			while (rs.next()) {
				String[] tempStrings = new String[11];
				for (int i = 1; i <= 11; i++)
					tempStrings[i - 1] = rs.getString(i);
				flightsArrayList.add(tempStrings);

			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			
		}
		return flightsArrayList;

	}

	public static Vector<String[]> getFlightsByDepartureDateAndCity(
			String date, String dest, String origin) {
		Vector<String[]> flightsArrayList = new Vector<String[]>(0);
		String sql = "SELECT f.*,c1.tax FROM flights f left join cities c on f.dest=c.id left join cities c1 on f.origin=c1.id"
				+ " where c.city='"
				+ dest
				+ "' and c1.city='"
				+ origin
				+ "' and f.start_date='" + date + "'";
		System.out.println(sql);
		ResultSet rs;
		try {
			rs = DataBaseManager.getConnection().createStatement()
					.executeQuery(sql);

			while (rs.next()) {
				String[] tempStrings = new String[11];
				for (int i = 1; i <= 11; i++)
					tempStrings[i - 1] = rs.getString(i);
				flightsArrayList.add(tempStrings);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flightsArrayList;

	}

	public static ArrayList<String[]> getBookings(String userid) {
		String sql = "SELECT mt.make_order,EF.id,ef.start_date,ef.start_time,mt.price,c_o.city,c_dest.city FROM "
				+

				" booked mt LEFT JOIN flights EF ON MT.flight_id=EF.ID left join cities c_dest on ef.dest=c_dest.id left join cities c_o on c_o.id=ef.origin "
				+ " WHERE  mt.passenger='" + userid + "'";

		ArrayList<String[]> ret = new ArrayList<String[]>();
		System.out.println(sql);
		try {
			ResultSet rs = DataBaseManager.getConnection().createStatement()
					.executeQuery(sql);
			while (rs.next()) {
				String[] itemStrings = new String[7];
				for (int i = 0; i < 7; i++)
					itemStrings[i] = rs.getString(i + 1);
				ret.add(itemStrings);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ret;

	}

}
